"""
Case Type   : openGauss 支持忽略PLSQL间的依赖进行创建
Case Name   : 自定义函数返回值类型是自定义类型，自定义类型不存在
Create At   : 2023.11.23
Owner       : zhanghuan96
Description :
    1. 使用gs_guc reload方式设置参数为plpgsql_dependency
    2. 创建自定义函数，返回值是不存在的自定义类型
    3. 查询系统表
    4. 创建类型
    5. 查询系统表
    6. 删除类型
    7. 查询依赖对象的系统表
    8. 删除函数
    9. 查询系统表
    10.清理环境
Expect      :
    1. 成功
    2. 有告警，创建成功
    3. valid字段返回f
    4. 成功
    5. valid字段返回f
    6. 成功
    7. 成功
    8. 成功
    9. valid字段为空
    10. 成功
History     :
"""
import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node


class PlpgsqlDependency(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f"-----{os.path.basename(__file__)} start-----")
        self.common = Common()
        self.constant = Constant()
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_node = Node('PrimaryDbUser')
        self.type_name = "type_guc0038"
        self.func_name = "func_guc0038"
        self.default_value = self.common.show_param('behavior_compat_options')

    def test_guc(self):
        text = '--step1:使用gs_guc reload方式设置参数为plpgsql_dependency;' \
               'expect:成功--'
        self.log.info(text)
        mod_msg = self.pri_sh.execute_gsguc('reload',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            "behavior_compat_options "
                                            "='plpgsql_dependency'")
        self.log.info(mod_msg)
        self.assertTrue(mod_msg)
        sql_cmd = "show behavior_compat_options;"
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertEqual('plpgsql_dependency',
                         sql_res.splitlines()[-2].strip(),
                         '查询失败')

        text = '--step2:创建自定义函数，返回值是不存在的自定义类型;' \
               'expect:有告警，创建成功--'
        self.log.info(text)
        sql_cmd = f'''drop function if exists {self.func_name};
        create or replace function {self.func_name}() return \
        {self.type_name} as
        begin
            null;
        end;  
        '''
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn('WARNING:  Type type_guc0038 does not exist', sql_res,
                      '查询失败')
        self.assertIn('CREATE FUNCTION', sql_res, '查询失败')

        text = '--step3:查询系统表;expect:valid字段返回f--'
        self.log.info(text)
        sql_cmd = f'''select valid from pg_object where object_type='P' \
        and object_oid in (select Oid from pg_proc where propackageid = 0 \
        and proname='{self.func_name}' and pronamespace = \
        (select Oid from pg_namespace where nspname = 'public'));'''
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn('valid \n-------\n f\n(1 row)', sql_res, '查询失败')

        self.log.info('依赖对象的系统表成功')
        sql_cmd = f'''select a.schemaname as sn, a.packagename as pn, \
        a.objectname as on, a.refobjpos as refpos,
         b.schemaname as refsn, b.packagename as refpn, b.name as refon, \
         b.type as refot, b.objnode as refast
         from gs_dependencies as a right join gs_dependencies_obj as b on \
         b.oid = a.refobjoid where (a.schemaname='public' and \
         a.objectname = 'func_0038()') or (b.schemaname='public' and \
         b.name = '{self.func_name}()') order by a.schemaname, a.objectname, \
         a.refobjpos, b.type, b.name;
        '''
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn('| public | null  | func_guc0038() |     5 |', sql_res,
                      '查询失败')

        text = '--step4:创建类型;expect:成功--'
        self.log.info(text)
        sql_cmd = f'''drop type if exists {self.type_name} cascade;
        create type {self.type_name} as (a int, b int);'''
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn('CREATE TYPE', sql_res, '查询失败')

        text = '--step5:查询系统表;expect:valid字段返回f--'
        self.log.info(text)
        sql_cmd = f'''select valid from pg_object where object_type='P' \
       and object_oid in (select Oid from pg_proc where propackageid = 0 \
       and proname='{self.func_name}' and pronamespace = \
       (select Oid from pg_namespace where nspname = 'public'));'''
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn('valid \n-------\n f\n(1 row)', sql_res, '查询失败')

        text = '--step6:删除类型;expect:成功--'
        self.log.info(text)
        sql_cmd = f'''drop type {self.type_name};'''
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn('DROP TYPE', sql_res, '查询失败')

        text = '--step7:查询依赖对象的系统表;expect:成功--'
        self.log.info(text)
        sql_cmd = f'''select a.schemaname as sn, a.packagename as pn, \
        a.objectname as on, a.refobjpos as refpos,
         b.schemaname as refsn, b.packagename as refpn, b.name as refon, \
         b.type as refot, b.objnode as refast
         from gs_dependencies as a right join gs_dependencies_obj as b on \
         b.oid = a.refobjoid where (a.schemaname='public' and \
         a.objectname = 'func_0038()') or (b.schemaname='public' and \
         b.name = '{self.func_name}()') order by a.schemaname, a.objectname, \
         a.refobjpos, b.type, b.name;
        '''
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn(' public | null  | func_guc0038() |     5 |', sql_res,
                      '查询失败')

        text = '--step8:删除函数;expect:成功--'
        self.log.info(text)
        sql_cmd = f'''drop function {self.func_name};'''
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn('DROP FUNCTION', sql_res, '查询失败')

        text = '--step9:查询系统表;expect:valid字段为空--'
        self.log.info(text)
        sql_cmd = f'''select valid from pg_object where object_type='P' \
       and object_oid in (select Oid from pg_proc where propackageid = 0 \
       and proname='{self.func_name}' and pronamespace = \
       (select Oid from pg_namespace where nspname = 'public'));'''
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn('valid \n-------\n(0 rows)', sql_res, '查询失败')

        self.log.info('依赖对象的系统表为空')
        sql_cmd = f'''select a.schemaname as sn, a.packagename as pn, \
        a.objectname as on, a.refobjpos as refpos,
         b.schemaname as refsn, b.packagename as refpn, b.name as refon, \
         b.type as refot, b.objnode as refast
         from gs_dependencies as a right join gs_dependencies_obj as b on \
         b.oid = a.refobjoid where (a.schemaname='public' and \
         a.objectname = 'func_0038()') or (b.schemaname='public' and \
         b.name = '{self.func_name}()') order by a.schemaname, a.objectname, \
         a.refobjpos, b.type, b.name;
        '''
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn('(0 rows)', sql_res, '查询失败')

    def tearDown(self):
        text = '----step10:清理环境;expect:成功----'
        self.log.info(text)
        mod_msg = self.pri_sh.execute_gsguc('reload',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            f"behavior_compat_options "
                                            f"='{self.default_value}'")
        self.log.info(mod_msg)
        self.assertTrue(mod_msg)
        self.log.info(f"-----{os.path.basename(__file__)} end-----")
